USE [BAMPrimaryImport]
GO

/****** Object:  StoredProcedure [dbo].[edi_Tools_GetNewInterchanges]    Script Date: 06/22/2009 13:00:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[edi_Tools_GetNewInterchanges]

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT  
	    bam_InterchangeStatusActivity_Completed.InterchangeControlNo, 
		bam_FunctionalGroupInfo_Completed.GroupControlNo, 
		bam_FunctionalGroupInfo_Completed.TSCount, 
		bam_InterchangeStatusActivity_Completed.GroupCount, 
		bam_FunctionalGroupInfo_Completed.FunctionalIDCode,  
		bam_InterchangeStatusActivity_Completed.ReceiverQ, 
		bam_InterchangeStatusActivity_Completed.ReceiverID, 
		bam_InterchangeStatusActivity_Completed.SenderQ, 
		bam_InterchangeStatusActivity_Completed.SenderID, 
		bam_InterchangeStatusActivity_Completed.IsInterchangeAckExpected, 
		bam_InterchangeStatusActivity_Completed.IsFunctionalAckExpected,
		bam_InterchangeStatusActivity_Completed.TsCorrelationId
		
	FROM         
		bam_FunctionalGroupInfo_Completed 
		INNER JOIN
			bam_InterchangeStatusActivity_Completed ON 
				bam_FunctionalGroupInfo_Completed.InterchangeActivityID = bam_InterchangeStatusActivity_Completed.ActivityID 
	WHERE
		bam_InterchangeStatusActivity_Completed.Direction=2
		and
		bam_FunctionalGroupInfo_Completed.FunctionalIDCode <> 'FA'
		and
		bam_FunctionalGroupInfo_Completed.PickedUp is NULL
		
		
	UPDATE
		bam_FunctionalGroupInfo_Completed
	SET
		bam_FunctionalGroupInfo_Completed.PickedUp='Y'
	WHERE
		bam_FunctionalGroupInfo_Completed.PickedUp is NULL
		
END



GO


